SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROC [dbo].[SP_TASALD_EMPR_Q18]  
/*---------------------------------------------------------------------*/  
/*----- Empresa          : OFISIS S.A.                            -----*/  
/*----- Cliente          : OFISIS S.A.                            -----*/  
/*----- Sistema          : Contabilidad                           -----*/  
/*----- Modulo           : Empresa                                -----*/  
/*----- Programa         : Reporte de ajuste por inflacion        -----*/  
/*----- Nombre           : SP_TASALD_EMPR_Q18                   -----*/  
/*----- Script           : TASALD_EMPR                            -----*/  
/*----- Desarrollado     : Betsy Sanchez Arenas                   -----*/  
/*----- Fecha            : 13/10/1997                             -----*/  
/*----- Base de Datos    : MS SQL Server                          -----*/  
/*----- Version BD       : 8.0                                 -----*/  
/*----- Invoca a SP      :                                        -----*/  
/*---------------------------------------------------------------------*/  
/*---------------------------------------------------------------------*/  
/*----- Modificado 1 por : Martin Garcia Farfan                   -----*/  
/*----- Fecha Modifica   : 12/03/1999                             -----*/  
/*----- Detalle Modifica : Estandarizacion                        -----*/  
/*---------------------------------------------------------------------*/  
/*---------------------------------------------------------------------*/  
/*----- Modificado 2 por : Marco Acua Paredes                   -----*/  
/*----- Fecha Modifica   : 25/09/2002             -----*/  
/*----- Detalle Modifica : Modificaci?n de cursor  - Nueva Versi?n                -----*/  
/*---------------------------------------------------------------------*/  
/*---------------------------------------------------------------------*/  
/*----- Comentarios      : Reporte de Saldos por mes              -----*/  
/*-----                    hasta el saldo actual                  -----*/  
/*---------------------------------------------------------------------*/  
/*---------------------------------------------------------------------*/  
/*----- Drop Proc SP_TASALD_EMPR_Q18                              -----*/  
/*----- Grant Execute On SP_TASALD_EMPR_Q18 To PUBLIC             -----*/  
/*----- EXEC SP_TASALD_EMPR_Q18 '01',1,2002,4,'42160',1800746.87,'C',5  
-----*/  
/*---------------------------------------------------------------------*/  
@ISCO_EMPR             TD_VC_002,  
@INNU_CNTB_EMPR       TD_IN_001_CONT,  
@INNU_ANNO             TD_IN_004_ANNO,  
@INNU_MESE             TD_IN_002_MESE,  
@ISCO_CNTA            TD_VC_020_CNTA,  
@INVA_SALD            TD_NU_016_004,  
@ISTI_SALD           TD_VC_001,  
@INNV_INFO_CNTA  TD_IN_001  
As  
Create Table #TWSALD_EMPR_Q18_1  
(NU_PERI      Char(7)   null,  
 HT_CARG      Numeric(16,4)     null,  
 HT_ABON      Numeric(16,4)     null,  
 FA_ACTU      Float     null,  
 AT_CARG     Numeric(16,4)     null,  
 AT_ABON      Numeric(16,4)     null,  
 VA_REI       Numeric(16,4)     null)  
Declare   
 @CNNU_ANNO      TD_IN_004_ANNO,  
 @CNNU_MESE      TD_IN_002_MESE,  
 @VSNU_PERI      char(7),   
 @VNHT_CARG      TD_NU_016_004,    @VNHT_ABON      TD_NU_016_004,    
 @VNFA_ACTU      TD_NU_009_004,  
 @VNAT_CARG      TD_NU_016_004,  
 @VNAT_ABON      TD_NU_016_004,      
 @CNIM_CARG_MESE TD_NU_016_004,  
 @CNIM_ABON_MESE TD_NU_016_004,  
 @VNVA_REIN      TD_NU_016_004,  
 @VNAC_ABON      TD_NU_016_004,      
 @VNAC_CARG      TD_NU_016_004,  
 @VNNU_IND1      TD_NU_009_004,  
 @VNNU_IND2   TD_NU_009_004,  
 @VNNU_FLAG      TD_IN_001  
   
 /* inicializo variables  */  
Select   
@CNIM_CARG_MESE = 0.00,  
@CNIM_ABON_MESE = 0.00,  
@VNHT_CARG = 0,  
@VNHT_ABON = 0,  
@VNFA_ACTU = 0,  
@VNAT_CARG = 0,  
@VNAT_ABON = 0,  
@VNVA_REIN  = 0,  
@VNNU_IND1  = 0.00,  
@VNNU_IND2  = 0.00,  
@VNAC_CARG = 0.00,   
@VNAC_ABON = 0.00,  
@VNNU_FLAG    = 0  
/* obtengo indice de actualizacion a mes actual */  
Select @VNNU_IND2 = IN_ACTU From   TTINDI_ACTU     
Where  NU_ANNO = @INNU_ANNO   
And NU_MESE = @INNU_MESE  
If @VNNU_IND2 IS null or @VNNU_IND2 = 0.00  
 Select @VNNU_IND2 = 1.00 /* declaro Cursor para obtener el importe de lAs ctAs para cada periodo */  
/* Saldo por mes (hasta el saldo actual) */  
Declare CU_TASALD_EMPR_Q18_1 Cursor  
for  
 Select NU_ANNO, NU_MESE, SUM(IM_CARG_CNTB), SUM(IM_ABON_CNTB)  
  From TASALD_EMPR   
  Where  CO_EMPR      = @ISCO_EMPR  
  And    NU_CNTB_EMPR = @INNU_CNTB_EMPR  
  And    NU_ANNO       = @INNU_ANNO  
  And    NU_MESE       <= @INNU_MESE  
  And    CO_CNTA_EMPR = Rtrim(@ISCO_CNTA)  
  And    datalength(rtrim(CO_CNTA_EMPR)) = @INNV_INFO_CNTA  
  And    TI_AUXI_EMPR IS null  
And      CO_AUXI_EMPR IS null  
And      CO_ORDE_SERV IS null  
  Group By CO_CNTA_EMPR, NU_ANNO, NU_MESE 
  Order By CO_CNTA_EMPR, NU_ANNO, NU_MESE desc  
   
 Open CU_TASALD_EMPR_Q18_1  
 Fetch CU_TASALD_EMPR_Q18_1 Into @CNNU_ANNO, @CNNU_MESE, @CNIM_CARG_MESE, @CNIM_ABON_MESE  
While @@Fetch_status = 0  
 Begin  
  /* obtengo indice de actualizacion a mes historico */  
   Select @VNNU_IND1 = isnull(IN_ACTU,1)  
   From   TTINDI_ACTU     
   Where  NU_ANNO = @CNNU_ANNO   
   And NU_MESE = @CNNU_MESE  
  If @VNNU_IND1 IS null or @VNNU_IND1 = 0.00  
   Select @VNNU_IND1 = 1.00  
    
  Select @VNFA_ACTU = @VNNU_IND2 / @VNNU_IND1  
  Select @VNFA_ACTU = round(@VNFA_ACTU,3 )  
  If @ISTI_SALD =  'E'  
   Begin  
    Select @VNHT_CARG = @CNIM_CARG_MESE  
    Select @VNHT_ABON = 0.00  
    If (@VNAC_CARG + @VNHT_CARG) > @INVA_SALD   
     Begin  
      Select @VNHT_CARG = @INVA_SALD - @VNAC_CARG  
      Select @VNNU_FLAG = 1  
     End  
    Else   
     Begin  
      Select @VNAC_CARG = @VNAC_CARG + @VNHT_CARG  
     End  
    Select @VNAT_CARG = @VNHT_CARG * @VNFA_ACTU  
    Select @VNAT_ABON = 0.00  
    Select @VNVA_REIN = @VNAT_CARG - @VNHT_CARG  
    Select @VSNU_PERI = convert(char(4),@CNNU_ANNO) + "-" +   
           SubString(convert(char(3),@CNNU_MESE+100),2,3)  
   
    Insert Into #TWSALD_EMPR_Q18_1 Values(@VSNU_PERI, @VNHT_CARG, @VNHT_ABON, @VNFA_ACTU,  
 @VNAT_CARG, @VNAT_ABON, @VNVA_REIN )  
   If @VNNU_FLAG = 1  
    Begin  
     Break  
    End       
   End   
  Else  
   If @ISTI_SALD =  'C'  
    Begin  
     Select @VNHT_CARG = 0.00  
     Select @VNHT_ABON = @CNIM_ABON_MESE  
     If (@VNAC_ABON + @VNHT_ABON) > abs(@INVA_SALD)  
      Begin  
  
  
       Select @VNHT_ABON = abs(@INVA_SALD) - @VNAC_ABON  
       Select @VNNU_FLAG = 1  
  
---#########  
--SELECT @CNNU_ANNO, @CNNU_MESE, @VNAC_ABON , @INVA_SALD , @VNHT_ABON  
  
      End  
     Else   
      Begin  
       Select @VNAC_ABON = @VNAC_ABON + @VNHT_ABON  
  
---#########  
---SELECT @CNNU_ANNO, @CNNU_MESE, @VNAC_ABON , @INVA_SALD , @VNHT_ABON  
  
      End  
     Select @VNAT_ABON = @VNHT_ABON * @VNFA_ACTU  
     Select @VNAT_CARG = 0.00  
     Select @VNVA_REIN = @VNAT_ABON - @VNHT_ABON  
     Select @VSNU_PERI = convert(char(4),@CNNU_ANNO) + "-" + SubString(convert(char(3),@CNNU_MESE+100),2,3)  
   
  
     Insert Into #TWSALD_EMPR_Q18_1 Values(@VSNU_PERI, @VNHT_CARG, @VNHT_ABON, @VNFA_ACTU,  
 @VNAT_CARG, @VNAT_ABON, @VNVA_REIN )  
    If @VNNU_FLAG = 1  
     Begin                               
      Break  
     End                         
   End  
    
   Fetch CU_TASALD_EMPR_Q18_1 Into @CNNU_ANNO, @CNNU_MESE, @CNIM_CARG_MESE, @CNIM_ABON_MESE  
  End  
   
 Close CU_TASALD_EMPR_Q18_1  
 Deallocate  CU_TASALD_EMPR_Q18_1  
 Select NU_PERI,  HT_CARG,  HT_ABON, FA_ACTU, AT_CARG, AT_ABON, VA_REI  
 From #TWSALD_EMPR_Q18_1  
 Return    
/*-------------------------------- Fin ---------------------------------------*/  
  
  
  
  
  
  
  
  


GO
